[LeetCode]180. Consecutive Numbers

問題描述

Write a SQL query to find all numbers that appear at least three times consecutively.

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

ConsecutiveNums
1

翻譯

請寫出一段 SQL 查詢能找出所有連續出現三次以上的數字

解題思維

利用 LEAD(), LAG() Oracle 分析函數可分別得出該數字的上下數字，再判斷上下數字、與該數字都是否相等，即為答案。

解題報告

Level: Medium
Runtime: 678 ms, faster than 98.93% of Oracle online submissions for Consecutive Numbers.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Consecutive Numbers.

程式完整解題

/* Write your PL/SQL query statement below */
SELECT NUM AS ConsecutiveNums
  FROM(SELECT NUM
             ,LEAD(NUM) OVER (ORDER BY ID) AS NEXT_NUM
             ,LAG(NUM)  OVER (ORDER BY ID) AS LAST_NUM
         FROM Logs)
 WHERE NEXT_NUM = LAST_NUM
   AND NEXT_NUM = NUM
 GROUP BY NUM;

SQL Schema

Create table If Not Exists Logs (Id int, Num int)
Truncate table Logs
insert into Logs (Id, Num) values ('1', '1')
insert into Logs (Id, Num) values ('2', '1')
insert into Logs (Id, Num) values ('3', '1')
insert into Logs (Id, Num) values ('4', '2')
insert into Logs (Id, Num) values ('5', '1')
insert into Logs (Id, Num) values ('6', '2')
insert into Logs (Id, Num) values ('7', '2')